OpenEdge Data Management:<br />SQL Development Triggers, stored procedures, and constraints

Triggers are identical to stored procedures in many respects. There are three main differences:

Triggers are automatic. When the trigger event (an INSERT, UPDATE, or DELETE statement) affects the specified table (and, optionally in UPDATE operations, the specified columns), the Java code contained in the body of the trigger executes. Stored procedures, on the other hand, must be explicitly invoked by an application or another procedure.

Triggers cannot have output parameters or a result set. Since triggers are automatic, there is no calling application to process any output they might generate. The practical consequence of this is that the Java code in the trigger body cannot invoke methods of the DhSQLResultSet class.

Triggers have limited input parameters. The only possible input parameters for triggers are values of columns in the rows affected by the trigger event. If the trigger includes the REFERENCING clause, OpenEdge SQL passes the values (either as they existed in the database or are specified in the INSERT or UPDATE statement) of each row affected. The Java code in the trigger body can use those values in its processing by invoking the getValue method of the OLDROW and NEWROW objects.

The automatic nature of triggers makes them well suited for enforcing referential integrity. In this regard they are like constraints, since both triggers and constraints can help ensure that a value stored in the foreign key of a table must either be null or be equal to some value in the matching unique or primary key of another table. However, triggers differ from constraints in the following ways:

Triggers are active, while constraints are passive. Constraints prevent updates that violate referential integrity, and triggers perform explicit actions in addition to the update operation.

Triggers can do much more than enforce referential integrity. Because they are passive, constraints are limited to preventing updates in a narrow set of conditions. Triggers are more flexible.